****************************************************************************
// Purpose: Various merge samples generated from the MFR sample
****************************************************************************
do "D:\Data\workdata\708296\Project - Mother groups and mental health\Do\global.do" 


// the MFR sample

	use  "$work\mfr_sample.dta", replace
	

*******************************************
* make a pnr list of kids
* and their parent ids + kid yob/dob
*******************************************

keep pnr mfr_yob mfr_dob id_*  mfr_muni	
save "$work/mfr_kids.dta",replace

*******************************************
* a list of all relevant mom dad kids as pnrs
* for NOVAX merge but also merge with GP regs
*******************************************


use "$work\mfr_sample", clear
expand 3
keep pnr mfr_yob mfr_dob id_* 	


bys pnr: gen help=_n

gen mom=1 if help==1
gen dad=1 if help==2
gen kid=1 if help==3

rename pnr pnrb

gen pnr=""
replace pnr=pnrb if help==3
replace pnr=id_mother if help==1
replace pnr=id_father if help==2

keep pnr dad mom kid 

duplicates drop pnr, force
drop if pnr==""

save "$work\basicsample",replace
* this is a file of unique pnrs of kids dads moms

*******************************************
* mom and dad wide samples
*******************************************

use "$work\mfr_sample",clear


sort id_mother mfr_dob 
by id_mother mfr_dob: keep if _n == 1 
keep pnr id* mfr_yob mfr_dob mfr_muni
sort id_mother mfr_dob 
by id_mother: gen no = _n // observed max is 5
tab no 

* rehape wide mother* kids spell
preserve
reshape wide id_father  pnr mfr_dob mfr_yob mfr_muni, i(id_mother) j(no)
rename id_mother pnr
save "$work\themoms_wide.dta",replace 
restore

preserve
sort id_father mfr_dob
drop if id_father==""
by id_father: gen nof = _N
tab nof
drop if nof>5
drop nof

by id_father: gen nof = _n
tab nof
drop no

reshape wide id_mother pnr mfr_dob mfr_yob mfr_muni, i(id_father) j(nof)
rename id_father pnr
save "$work\thedads_wide.dta",replace 
restore

foreach num of numlist 1/5 {
preserve 
keep if no == `num'
save "$work\moms_no`num'", replace 
restore	
}

drop no

drop if id_father == ""
bys id_father: gen no=_N
drop if no>5
drop no

sort id_father mfr_dob 
by id_father: gen no = _n

foreach num of numlist 1/5{
preserve 
keep if no == `num'
drop id_mother no
save "$work\dads_no`num'", replace 
restore	
}



